﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Reflection;
using System.Data.OleDb;
using System.Data.Objects.SqlClient;

namespace proyectoENS
{
    public class excelInterop
    {
        public static void generarlistaasistencia(DateTime inicio, DateTime fin, int idCurso)
        {
            DataTable dt = new DataTable("Asistencia");
            DataRow dr;
            int cantColumnas = fin.Day - inicio.Day;
            for (int i = 0; i < cantColumnas + 1; i++)
            {
                DataColumn dc = new DataColumn();
                dc.DataType = System.Type.GetType("System.String");
                if (i == 0)
                    dc.ColumnName = "Alumno";
                else
                    dc.ColumnName = inicio.AddDays(i).ToShortDateString();
                dt.Columns.Add(dc);
            }
            foreach (Alumnos item in DALAlumnos.getAlumnosByIdCurso(idCurso))
            {
                dr = dt.NewRow();
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (i == 0)
                        dr[i] = item.Apellidos + ", " + item.nombres;
                    else
                    {
                        if ((DALAsistencias.getAsistenciaByAlumnoFecha(item.id, (DateTime)inicio.AddDays(i))) != null)
                            dr[i] = ((AsistenciaClases)DALAsistencias.getAsistenciaByAlumnoFecha(item.id, (DateTime)inicio.AddDays(i))).asistencia;
                        else dr[i] = "N/D";
                    }
                }
                dt.Rows.Add(dr);
            }
            Excel_FromDataTable(dt, @"C:\prueba.xls");
        }
        public static void Excel_FromDataTable(DataTable dt, string path)
        {
            
            Microsoft.Office.Interop.Excel.Application oXL;
            Microsoft.Office.Interop.Excel.Workbook oWB;
            Microsoft.Office.Interop.Excel.Worksheet oSheet;
            oXL = new Microsoft.Office.Interop.Excel.Application();
            oXL.Visible = false;
            oXL.DisplayAlerts = false;
            oWB = oXL.Workbooks.Add(Missing.Value);
            oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.ActiveSheet;
            oSheet.Name = "Asistencia";
            int rowCount = 2;
            oSheet.Cells[1, 1] = "Lista Asistencia";
            foreach (DataRow dr in dt.Rows)
            {
                rowCount += 1;
                for (int i = 1; i < dt.Columns.Count + 1; i++)
                {
                    {
                        
                        
                        oSheet.Cells[2, i] = dt.Columns[i - 1].ColumnName;
                    }

                    string value = dr[i - 1].ToString();
                    oSheet.Cells[rowCount, i] = "'" + value;
                }
            }
            oSheet.Columns.AutoFit();
            oSheet = null;

            oWB.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value);
            oWB.Close(Missing.Value, Missing.Value, Missing.Value);
            oWB = null;
            oXL.Quit();

            // Clean up 
            // NOTE: When in release mode, this does the trick 
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
        }
    }
}
